package com.edu.hre.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;



import com.edu.hre.db.config.BaseDAO;
import com.edu.hre.db.config.DBPool;
import com.edu.hre.db.dto.HreEmpEmergencyContact;
import com.edu.hre.db.dto.HreEmpDependent;
import com.edu.hre.db.dto.HreEmpLanguage;


public class EmpLanguageDAO  extends BaseDAO {
	private static final Logger logger = Logger.getLogger(EmpLanguageDAO.class);
	public List<HreEmpLanguage> getLanguageByEmployeeId(
			int employeeNumber) {
		List<HreEmpLanguage> listEmpLanguage = new ArrayList<HreEmpLanguage>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = 
					"SELECT el.*, l.name AS language_name FROM hre_emp_language el "+
					"JOIN hre_language l ON el.lang_id = l.id "+
					"WHERE emp_number = "
					+ employeeNumber;
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreEmpLanguage empLanguage = new HreEmpLanguage();
				empLanguage.setComments(rs.getString("comments"));
				empLanguage.setLanguageName(rs.getString("language_name"));
				empLanguage.setReadingLevel(rs.getShort("read_competency")/2 -1);
				empLanguage.setWritingLevel(rs.getShort("write_competency")/2 -1);
				empLanguage.setListeningLevel(rs.getShort("listen_competency")/2 -1);
				empLanguage.setSpeakingLevel(rs.getShort("speak_competency")/2 -1);
				//.....
				//.....
				listEmpLanguage.add(empLanguage);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào pool
			releaseConnection(conn, preStmt, rs);
		}
		
		return listEmpLanguage;
	}

}
